/**
Company: Sarkar software technologys
WebSite: http://www.sarkar.cn
Author: yeganaaa
Date : 6/13/18
Time: 3:09 PM
 */

package Cn.Sarkar.MMS.WebAPI

import Cn.Sarkar.Entities.MMS.Additional.ICountry
import Cn.Sarkar.Entities.MMS.ToJsonEntities.JCountry
import Cn.Sarkar.EntityDream.CoreEngine.RDBMS.Core.QueryBuilderExtensions.SelectQueryExpression.countDistinct
import Cn.Sarkar.EntityDream.CoreEngine.RDBMS.Core.QueryBuilderExtensions.SelectQueryExpression.equals
import Cn.Sarkar.EntityDream.CoreEngine.RDBMS.Core.QueryBuilderExtensions.SelectQueryExpression.singleOrNull
import Cn.Sarkar.MMS.DataBaseDefinations.Additional.Countries
import Cn.Sarkar.MMS.Entities.Additional.Country
import Cn.Sarkar.MMS.Entities.Additional.Name
import Cn.Sarkar.MMS.Entities.DataSources.IDataSource
import Cn.Sarkar.MMS.LayerOne.convertToJ
import Cn.Sarkar.ResponseModels.success
import com.google.gson.Gson
import io.ktor.http.HttpStatusCode
import io.ktor.locations.Location
import io.ktor.locations.get
import io.ktor.locations.location
import io.ktor.response.respond
import io.ktor.routing.Route
import io.ktor.routing.get
import io.ktor.routing.put
import java.io.File
import java.sql.ResultSet
import java.util.*


object AreaLocations {
    @Location("Area")
    class AreaLocation

    @Location("Search")
    data class SearchLocation(var Layer: Int = 0, var KeyWord: String = "", var From: Int = 0, var Count: Int = 100) {
        val keyWordIsEmpty: Boolean
            get() = KeyWord == ""
    }

    @Location("FromID")
    data class FromIDLocation(var ID: Int)

    @Location("Childs")
    data class ChildsLocation(var ID: Int)

}

private data class Area(var ID: Int, var Zh: String, var ParentID: Int?, var Ug: String?, var Childs: LinkedList<Area> = LinkedList()) {
    companion object {
        fun importFromLocal() {

            IDataSource.openAndAutoClose { db ->

                val sortedData = loadSortArea()
                var layer = 0

                fun recursiveArea(base: Country?, area: Area) {
                    val countryName = Name(db).apply {
                        ug = area.Ug ?: ""
                        zh = area.Zh
                        category = Countries.CATEGORY
                    }
                    db.names.add(countryName)
                    db.saveChanges()

                    val country = Country(db).apply {
                        this.name = countryName
                        latitude = ""
                        longitude = ""
                        this.layer = layer
                    }

                    if (base != null){
                        country.parent = base
                    }

                    db.countries.add(country)
                    db.saveChanges()

                    if (area.Childs.size > 0) {
                        layer++
                        area.Childs.forEach {
                            recursiveArea(country, it)
                        }
                        layer--
                    }
                }

                sortedData.forEach {
                    layer = 0
                    recursiveArea(null, it)
                }
            }

            println("ok")
        }


        private fun loadSortArea(): Array<Area> {
            val data = Gson().fromJson(File("Area.json").readText(), Array<Area>::class.java)
            data.forEach { if (it.Childs == null) it.Childs = LinkedList() }
            val list = LinkedList<Area>()
            list.addAll(data)

            val listTwo = LinkedList<Area>()
            listTwo.addAll(data)

            list.sortByDescending { it.ParentID }

            list.forEach { area ->
                val child1 = listTwo.firstOrNull { it.ID == area.ParentID }
                if (child1 != null) {
                    child1.Childs.add(area)
                    listTwo.remove(area)
                }
            }

            return listTwo.toTypedArray()
        }
    }
}

fun Route.useAreaController() {
    val resultReader = object {

        val cols = object {
            val id = "ID"
            val layer = "Layer"
            val ug = "Ug"
            val zh = "Zh"
            val parentId = "ParentID"
        }

        fun readArea(resultSet: ResultSet): Array<ICountry> = resultSet.run result@{
            val retv = LinkedList<JCountry>()
            while (next()) {
                retv.add(JCountry().apply {
                    this.id = this@result.getInt(cols.id)
                    this.parentId = this@result.getInt(cols.parentId)
                    this.layer = this@result.getInt(cols.layer)
                    this.name.apply {
                        ug = this@result.getString(cols.ug)
                        zh = this@result.getString(cols.zh)
                    }
                })
            }
            close()
            retv.toTypedArray()
        }
    }


    location<AreaLocations.AreaLocation> {
        put("ImportFromLocal") {
            success {
                IDataSource.openAndAutoClose { db ->
                    var result = "data allready exists"
                    if (db.countries.size == 0) {
                        Area.importFromLocal()
                        result = "Success"
                    }
                    result
                }
            }
        }

        get("AllLayers") {
            IDataSource.openAndAutoClose {
                val all = it.countries.countDistinct(Countries.Layer)
                context.respond(all)
            }
        }

        get<AreaLocations.SearchLocation> { params ->
            val data = IDataSource.openAndAutoClose { db ->

                val sqlQuery = if (params.keyWordIsEmpty)
                    "select Country.ID as ${resultReader.cols.id}, Country.ParentID as ${resultReader.cols.parentId}, Country.Layer as ${resultReader.cols.layer}, Name.Ug as ${resultReader.cols.ug}, Name.Zh as ${resultReader.cols.zh} from Country inner join Name on Country.NameID = Name.ID where Country.Layer = ? limit ?, ?"
                else
                    "select Country.ID as ${resultReader.cols.id}, Country.ParentID as ${resultReader.cols.parentId}, Country.Layer as ${resultReader.cols.layer}, Name.Ug as ${resultReader.cols.ug}, Name.Zh as ${resultReader.cols.zh} from Country inner join Name on Country.NameID = Name.ID where (Name.Ug like ? or Name.Zh like ?) and (Country.Layer = ?) limit ?, ?"
                val statement = db.connection.prepareStatement(sqlQuery)


                if (!params.keyWordIsEmpty) {
                    val word = '%' + params.KeyWord + '%'
                    statement.apply {
                        setString(1, word)
                        setString(2, word)
                        setInt(3, params.Layer)
                        setInt(4, params.From)
                        setInt(5, params.Count)
                    }
                } else {
                    statement.apply {
                        setInt(1, params.Layer)
                        setInt(2, params.From)
                        setInt(3, params.Count)
                    }
                }
                resultReader.readArea(statement.executeQuery())
            }
            context.respond(data)
        }

        get<AreaLocations.FromIDLocation> { params ->
            val data = IDataSource.openAndAutoClose { db ->
                db.countries.singleOrNull { Countries.ID equals params.ID }?.convertToJ()
            }
            context.respond(data ?: HttpStatusCode.NotFound)
        }

        get<AreaLocations.ChildsLocation> { params ->
            val data = IDataSource.openAndAutoClose {
                val statement = it.connection.prepareStatement("select selectedCountry.ID, selectedCountry.Layer, selectedCountry.ParentID, Name.Ug, Name.Zh from Name inner join (select Country.ID, Country.Layer, Country.NameID, Country.ParentID from Country where Country.ParentID = ?) selectedCountry on selectedCountry.NameID = Name.ID;")
                statement.setInt(1, params.ID)
                resultReader.readArea(statement.executeQuery())

            }
            context.respond(data)
        }
    }
}

